<?php

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;

class DefaultDb_Repositories_RouteRepository extends EntityRepository
{

    public function existByCode($code)
    {
        $em = $this->getEntityManager();
        $dql = 'SELECT r.id
                FROM DefaultDb_Entities_Route r
                WHERE r.code LIKE :code';
        $query = $em->createQuery($dql);
        $query->setParameter('code', $code);
        $routes = $query->getResult();
        return count($routes) > 0;
    }

    public function isClose($rutaId)
    {
        $em = $this->getEntityManager();
        $dql = 'SELECT r.id
                FROM DefaultDb_Entities_Route r
                JOIN 
                WHERE r.code LIKE :code';
        $query = $em->createQuery($dql);
        $query->setParameter('code', $code);
        $routes = $query->getResult();
        return count($routes) > 0;
    }

    public function getDetailsRoute($routeId)
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $query = "
            SELECT 
                  CONCAT('[',r.code,'] ',r.name) route_name
                , count(rp.id) total
                , SUM(IF(p.type = 1, 1, 0)) pv
                , SUM(IF(p.type = 2, 1, 0)) ci
                , SUM(IF(rp.required = 1, 1, 0)) requireds
                , FLOOR( SUM(TIME_TO_SEC(rp.arrival_time)) / 60 ) minutes
                , IF(r.close = 1, 'CERRADA', 'ABIERTA') str_close
            FROM `route_points`  rp
            INNER JOIN routes r ON rp.route_id = r.id
            INNER JOIN points p ON rp.point_id = p.id
            WHERE rp.route_id = " . $routeId . " AND rp.status = " . DefaultDb_Entities_Route::STATUS_ACTIVE;
        $res = $conn->executeQuery($query);
        $res = $res->fetch(PDO::FETCH_ORI_FIRST);
        return $res;
    }

}